﻿// MIT License
//
// Copyright (c) 2021-present zuohuaijun, Daming Co.,Ltd and Contributors
//
// 电话/微信：18020030720 QQ群1：87333204 QQ群2：252381476

using Admin.NET.Application.Const;
using Admin.Net.InformationPlatform.Entity;
using Furion.DependencyInjection;
using Furion.DynamicApiController;
using Microsoft.AspNetCore.Mvc;
using Nest;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Admin.NET.Core;
using Admin.Net.InformationPlatform.Entity.Equipment;
using Admin.Net.InformationPlatform.Service.Equipment.Dto;
using Admin.Net.InformationPlatform.Service.Equipment_Para.Dto;
using Admin.Net.InformationPlatform.Entity.ProdectFile;
using Furion.FriendlyException;
using Admin.Net.InformationPlatform.Entity.Maintenance;
using Admin.Net.InformationPlatform.Service.ProjectFile.Dto;
using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Security.Cryptography;
using Microsoft.AspNetCore.Authorization;
using Furion;

namespace Admin.Net.InformationPlatform.Service.Equipment_Para
{
    /// <summary>
    /// 华为验厂-设备参数管理
    /// </summary>
    [ApiDescriptionSettings(groups: "HWYC", Name = "设备参数管理", Tag = "设备参数管理", Order = 200)]

    public class EquipmentParaService : IDynamicApiController, ITransient
    {
        /// <summary>
        /// 单例数据链接
        /// </summary>
        ISqlSugarClient db;
        /// <summary>
        /// T100数据库
        /// </summary>
        private SqlSugarProvider _T100Db;
        private SqlSugarProvider _SjDb;
        private SqlSugarProvider _plm;
        private SqlSugarProvider _Dilon;


        /// <summary>
        /// 用户信息
        /// </summary>
        TokenUser _User;
        /// <summary>
        /// 设备参数管理
        /// </summary>
        /// <param name="db"></param>
        public EquipmentParaService(ISqlSugarClient db)
        {
            this.db = db;
            this._T100Db = this.db.AsTenant().GetConnection(T100TestArea.ConfigId);
            this._SjDb = this.db.AsTenant().GetConnection(SJDB.ConfigId);
            this._plm = this.db.AsTenant().GetConnection(PLM.ConfigId);
            this._Dilon = this.db.AsTenant().GetConnection(Dilon.ConfigId);
            // 获取 `Jwt` 存储的信息 企业编号与据点信息
            _User = Util.SjCommonUtil.GetT100UserTokenInfo(_T100Db, _Dilon);

        }
        ///// <summary>
        ///// 设备参数列表（分页）
        ///// </summary>
        ///// <param name="input"></param>
        ///// <returns></returns>
        //[HttpPost("/HWYC/GetSpecificInformationPagedList")]
        //public async Task<SqlSugarPagedList<HWSpecificInformationOutPut>> GetSpecificInformationPagedList(SelectEquipmentBasicInput input)
        //{
        //    var output = await _SjDb.Queryable<HWEquipmentBasicZi>()
        //        .LeftJoin<HWEquipmentBasicInformation>((z, l) => (z.DEVICENUMBER == l.DEVICENUMBER))
        //        .LeftJoin<HWSpecificInformation>((z, l, m) => (z.DEVICENUMBER == m.DEVICENUMBER && z.MATERIALNO == m.MATERIALNO))
        //    //  .Where((z, l, m) => l.LIFECYCLE == 0)
        //    // .WhereIF(!string.IsNullOrEmpty(input.MATERIALNO), (l) => (l.MATERIALNO.Contains(input.MATERIALNO)))//料号
        //    .WhereIF(!string.IsNullOrEmpty(input.DEVICENUMBER), (z) => (z.DEVICENUMBER.Contains(input.DEVICENUMBER)))//设备编号
        //    .OrderBy((z, l, m) => l.DEVICENUMBER) //创建日期排序
        //    .OrderBy((z, l, m) => l.CREATETIME, OrderByType.Desc) //创建日期排序
        //    .Select((z, l, m) => new HWSpecificInformationOutPut
        //    {
        //        EID = m.EID,
        //        MATERIALNO = z.MATERIALNO,
        //        ITEMNAME = z.ITEMNAME,
        //        EQUIPMENTSPECS = z.EQUIPMENTSPECS,
        //        EQUIPMENTTYPENO = l.EQUIPMENTTYPENO,
        //        DEVICENUMBER = l.DEVICENUMBER,
        //        STOVETEMPERATUREMAX = m.STOVETEMPERATUREMAX,
        //        STOVETEMPERATUREMIN = m.STOVETEMPERATUREMIN,
        //        BEAKTEMPERATUREMAX = m.BEAKTEMPERATUREMAX,
        //        BEAKTEMPERATUREMIN = m.BEAKTEMPERATUREMIN,
        //        GOOSENECKTEMPERATUREMAX = m.GOOSENECKTEMPERATUREMAX,
        //        GOOSENECKTEMPERATUREMIN = m.GOOSENECKTEMPERATUREMIN,
        //        EJECTTIMEMAX = m.EJECTTIMEMAX,
        //        EJECTTIMEMIN = m.EJECTTIMEMIN,
        //        COOLINGTIMEMAX = m.COOLINGTIMEMAX,
        //        COOLINGTIMEMIN = m.COOLINGTIMEMIN,
        //        SPRAYTIMEMAX = m.SPRAYTIMEMAX,
        //        SPRAYTIMEMIN = m.SPRAYTIMEMIN,
        //        LOCKMOULDFORCE_MAX = m.LOCKMOULDFORCE_MAX,
        //        LOCKMOULDFORCEMIN = m.LOCKMOULDFORCEMIN,
        //        TOTALPRESSUREMAX = m.TOTALPRESSUREMAX,
        //        TOTALPRESSUREMIN = m.TOTALPRESSUREMIN,
        //        MRONO = m.MRONO,
        //        MRONAME = m.MRONAME


        //    }).ToPagedListAsync(input.Page, input.PageSize);

        //    return output;

        //}

        /// <summary>
        /// 设备参数列表（分页）
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost("/HWYC/GetSpecificInformationPagedList")]
        public async Task<SqlSugarPagedList<HWSpecificInformation>> GetSpecificInformationPagedList(EquipmentBasicInput input)
        {
            var output = await _SjDb.Queryable<HWSpecificInformation>()
              
              .Where(l => l.ISDELETE == 0)
            .WhereIF(!string.IsNullOrEmpty(input.MATERIALNO), (l) => (l.MATERIALNO.Contains(input.MATERIALNO)))//料号
            .WhereIF(!string.IsNullOrEmpty(input.DEVICENUMBER), (l) => (l.DEVICENUMBER.Contains(input.DEVICENUMBER)))//设备编号
            .WhereIF(!string.IsNullOrEmpty(input.EQUIPMENTTYPENO), (l) => (l.EQUIPMENTTYPENO.Contains(input.EQUIPMENTTYPENO)))//设备类型编号
            .WhereIF(!string.IsNullOrEmpty(input.PRODUCTNAME), (l) => (l.ITEMNAME.Contains(input.PRODUCTNAME)))//品名
            .WhereIF(!string.IsNullOrEmpty(input.EQUIPMENTSPECS), (l) => (l.EQUIPMENTSPECS.Contains(input.EQUIPMENTSPECS)))//规格
          
            .OrderBy(l => l.CREATETIME, OrderByType.Desc) //创建日期排序
            .Select(l => new HWSpecificInformation
            {
                EID = l.EID,
                MATERIALNO = l.MATERIALNO,
                ITEMNAME = l.ITEMNAME,
                EQUIPMENTSPECS = l.EQUIPMENTSPECS,
                EQUIPMENTTYPENO = l.EQUIPMENTTYPENO,
                DEVICENUMBER = l.DEVICENUMBER,
                STOVETEMPERATUREMAX = l.STOVETEMPERATUREMAX,
                STOVETEMPERATUREMIN = l.STOVETEMPERATUREMIN,
                BEAKTEMPERATUREMAX = l.BEAKTEMPERATUREMAX,
                BEAKTEMPERATUREMIN = l.BEAKTEMPERATUREMIN,
                GOOSENECKTEMPERATUREMAX = l.GOOSENECKTEMPERATUREMAX,
                GOOSENECKTEMPERATUREMIN = l.GOOSENECKTEMPERATUREMIN,
                EJECTTIMEMAX = l.EJECTTIMEMAX,
                EJECTTIMEMIN = l.EJECTTIMEMIN,
                COOLINGTIMEMAX = l.COOLINGTIMEMAX,
                COOLINGTIMEMIN = l.COOLINGTIMEMIN,
                SPRAYTIMEMAX = l.SPRAYTIMEMAX,
                SPRAYTIMEMIN = l.SPRAYTIMEMIN,
                LOCKMOULDFORCE_MAX = l.LOCKMOULDFORCE_MAX,
                LOCKMOULDFORCEMIN = l.LOCKMOULDFORCEMIN,
                TOTALPRESSUREMAX = l.TOTALPRESSUREMAX,
                TOTALPRESSUREMIN = l.TOTALPRESSUREMIN,
                MRONO = l.MRONO,
                MRONAME = l.MRONAME,
                CREATETIME = l.CREATETIME,
                


            }).ToPagedListAsync(input.Page, input.PageSize);

            return output;

        }


        /// <summary>
        /// 设备保养预警看板（分页）
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost("/HWYC/GetSpecificInformationForewarning")]
        public async Task<SqlSugarPagedList<HWSpecificInformationYJ>> GetSpecificInformationForewarning(SelectEquipmentBasicInput input)
        {
            
            string sql = "";

            if (!string.IsNullOrEmpty(input.DEVICENUMBER))
                sql += $@" and b.DEVICE_NUMBER like '%{input.DEVICENUMBER}%'";
            return _SjDb.Ado.SqlQuery<HWSpecificInformationYJ>($@"
                 select b.*,l.* ,(CASE WHEN MAINTENANCE_TIME is not null  THEN MAINTENANCE_TIME 
        ELSE create_time END) AS newtime,to_date(to_char((CASE WHEN MAINTENANCE_TIME is not null  THEN MAINTENANCE_TIME 
        ELSE create_time END)+maintenance_intervals,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS') As nexttime from hw_equipment_basic_information b
 left join (    SELECT *
FROM (
  SELECT DEVICE_NUMBER, MAINTENANCE_TIME, ROW_NUMBER() OVER (PARTITION BY DEVICE_NUMBER ORDER BY MAINTENANCE_TIME DESC) AS rn
  FROM HW_MAINTENANCE_MESSAGE
) subquery
WHERE rn = 1) l on b.device_number = l.DEVICE_NUMBER
where b.life_cycle=0 {sql}
                ").ToPagedList(input.Page, input.PageSize);


        }
        ///// <summary>
        ///// 增加设备参数记录
        ///// </summary>
        ///// <returns></returns>
        //[HttpPost("/HWYC/AddHWSpecificInformation")]
        //public async Task<string> AddHWSpecificInformation(HWSpecificInformation input)
        //{
        //    //判断单据存在
        //    int cnt = await _SjDb.Queryable<HWSpecificInformation>()
        //        .Where(a => a.MATERIALNO == input.MATERIALNO)
        //        .Where(a => a.DEVICENUMBER == input.DEVICENUMBER)
        //        .Select(a => a.EID)
        //        .CountAsync();
        //    if (cnt != 0)//有数据时，修改数据
        //    {
        //        //提交
        //        try
        //        {
        //            _SjDb.Ado.BeginTran();
        //            int count = await _SjDb.Updateable<HWSpecificInformation>()

        //       .SetColumns(a => a.STOVETEMPERATUREMAX == input.STOVETEMPERATUREMAX)
        //       .SetColumns(a => a.STOVETEMPERATUREMIN == input.STOVETEMPERATUREMIN)
        //       .SetColumns(a => a.BEAKTEMPERATUREMAX == input.BEAKTEMPERATUREMAX)
        //       .SetColumns(a => a.BEAKTEMPERATUREMIN == input.BEAKTEMPERATUREMIN)
        //       .SetColumns(a => a.GOOSENECKTEMPERATUREMAX == input.GOOSENECKTEMPERATUREMAX)
        //       .SetColumns(a => a.GOOSENECKTEMPERATUREMIN == input.GOOSENECKTEMPERATUREMIN)
        //       .SetColumns(a => a.EJECTTIMEMAX == input.EJECTTIMEMAX)
        //       .SetColumns(a => a.EJECTTIMEMIN == input.EJECTTIMEMIN)
        //       .SetColumns(a => a.COOLINGTIMEMAX == input.COOLINGTIMEMAX)
        //       .SetColumns(a => a.COOLINGTIMEMIN == input.COOLINGTIMEMIN)
        //       .SetColumns(a => a.SPRAYTIMEMAX == input.SPRAYTIMEMAX)
        //       .SetColumns(a => a.SPRAYTIMEMIN == input.SPRAYTIMEMIN)
        //       .SetColumns(a => a.LOCKMOULDFORCE_MAX == input.LOCKMOULDFORCE_MAX)
        //       .SetColumns(a => a.LOCKMOULDFORCEMIN == input.LOCKMOULDFORCEMIN)
        //       .SetColumns(a => a.TOTALPRESSUREMAX == input.TOTALPRESSUREMAX)
        //       .SetColumns(a => a.TOTALPRESSUREMIN == input.TOTALPRESSUREMIN)
        //       .SetColumns(a => a.MRONO == input.MRONO)
        //       .SetColumns(a => a.MRONAME == input.MRONAME)
        //       .Where(a => a.EID == input.EID)

        //                .ExecuteCommandAsync();

        //            if (count != 1)
        //            {
        //                throw Oops.Oh("修改失败！");
        //            }
        //            _SjDb.Ado.CommitTran();
        //            return "修改成功！";
        //        }
        //        catch (Exception e)
        //        {
        //            _SjDb.Ado.RollbackTran();
        //            throw Oops.Oh($"异常：{e.Message}");
        //        }
        //    }
        //    else
        //    {
        //    var details = new HWSpecificInformation
        //    {
        //        MATERIALNO = input.MATERIALNO,
        //        DEVICENUMBER = input.DEVICENUMBER,
        //        STOVETEMPERATUREMAX = input.STOVETEMPERATUREMAX,
        //        STOVETEMPERATUREMIN = input.STOVETEMPERATUREMIN,
        //        BEAKTEMPERATUREMAX = input.BEAKTEMPERATUREMAX,
        //        BEAKTEMPERATUREMIN = input.BEAKTEMPERATUREMIN,
        //        GOOSENECKTEMPERATUREMAX = input.GOOSENECKTEMPERATUREMAX,
        //        GOOSENECKTEMPERATUREMIN = input.GOOSENECKTEMPERATUREMIN,
        //        EJECTTIMEMAX = input.EJECTTIMEMAX,
        //        EJECTTIMEMIN = input.EJECTTIMEMIN,
        //        COOLINGTIMEMAX = input.COOLINGTIMEMAX,
        //        COOLINGTIMEMIN = input.COOLINGTIMEMIN,
        //        SPRAYTIMEMAX = input.SPRAYTIMEMAX,
        //        SPRAYTIMEMIN = input.SPRAYTIMEMIN,
        //        LOCKMOULDFORCE_MAX = input.LOCKMOULDFORCE_MAX,
        //        LOCKMOULDFORCEMIN = input.LOCKMOULDFORCEMIN,
        //        TOTALPRESSUREMAX = input.TOTALPRESSUREMAX,
        //        TOTALPRESSUREMIN = input.TOTALPRESSUREMIN,
        //        MRONO = input.MRONO,
        //        MRONAME = input.MRONAME
        //    };
        //    #region 插入事务
        //    try
        //    {
        //        //开启事务
        //        _SjDb.Ado.BeginTran();
        //        int cnt1 = await _SjDb.Insertable<HWSpecificInformation>(details).ExecuteCommandAsync();

        //        if (cnt1 == 0)
        //        {
        //            throw Oops.Oh($"新建任务失败！");
        //        }
        //        //提交事务
        //        _SjDb.Ado.CommitTran();
        //        return "插入成功";
        //    }
        //    catch (Exception e)
        //    {
        //        //回滚事务
        //        _SjDb.Ado.RollbackTran();
        //        throw Oops.Oh($"异常：{e.Message}");
        //        }
        //    }
        //    #endregion

        //}

        /// <summary>
        /// 修改设备参数记录
        /// </summary>
        /// <returns></returns>
        [HttpPost("/HWYC/UpdateHWSpecificInformation")]
        public async Task<string> UpdateHWSpecificInformation(HWSpecificInformation input)
        {
            if (string.IsNullOrEmpty(input.MATERIALNO))
                throw Oops.Oh("料号不能为空");
            if (string.IsNullOrEmpty(input.EQUIPMENTTYPENO))
                throw Oops.Oh("设备类型编号不能为空");
            //判断单据存在
            int cnt = await _SjDb.Queryable<HWSpecificInformation>()
                .Where(a => a.EID == input.EID)
              
                    .Where(a => a.ISDELETE == 0)
                .Select(a => a.EID)
                .CountAsync();

            if (cnt == 0)
                throw Oops.Oh("此设备编号不存在");

            //提交
            try
                {
                    _SjDb.Ado.BeginTran();
                    int count = await _SjDb.Updateable<HWSpecificInformation>()

               .SetColumns(a => a.STOVETEMPERATUREMAX == input.STOVETEMPERATUREMAX)
               .SetColumns(a => a.STOVETEMPERATUREMIN == input.STOVETEMPERATUREMIN)
               .SetColumns(a => a.BEAKTEMPERATUREMAX == input.BEAKTEMPERATUREMAX)
               .SetColumns(a => a.BEAKTEMPERATUREMIN == input.BEAKTEMPERATUREMIN)
               .SetColumns(a => a.GOOSENECKTEMPERATUREMAX == input.GOOSENECKTEMPERATUREMAX)
               .SetColumns(a => a.GOOSENECKTEMPERATUREMIN == input.GOOSENECKTEMPERATUREMIN)
               .SetColumns(a => a.EJECTTIMEMAX == input.EJECTTIMEMAX)
               .SetColumns(a => a.EJECTTIMEMIN == input.EJECTTIMEMIN)
               .SetColumns(a => a.COOLINGTIMEMAX == input.COOLINGTIMEMAX)
               .SetColumns(a => a.COOLINGTIMEMIN == input.COOLINGTIMEMIN)
               .SetColumns(a => a.SPRAYTIMEMAX == input.SPRAYTIMEMAX)
               .SetColumns(a => a.SPRAYTIMEMIN == input.SPRAYTIMEMIN)
               .SetColumns(a => a.LOCKMOULDFORCE_MAX == input.LOCKMOULDFORCE_MAX)
               .SetColumns(a => a.LOCKMOULDFORCEMIN == input.LOCKMOULDFORCEMIN)
               .SetColumns(a => a.TOTALPRESSUREMAX == input.TOTALPRESSUREMAX)
               .SetColumns(a => a.TOTALPRESSUREMIN == input.TOTALPRESSUREMIN)
               .SetColumns(a => a.MRONO == input.MRONO)
               .SetColumns(a => a.MRONAME == input.MRONAME)
               .SetColumns(a => a.EQUIPMENTTYPENO == input.EQUIPMENTTYPENO.ToUpper())
               .SetColumns(a => a.ITEMNAME == input.ITEMNAME)
               .SetColumns(a => a.EQUIPMENTSPECS == input.EQUIPMENTSPECS)
               .SetColumns(a => a.DEVICENUMBER == input.DEVICENUMBER)
               .SetColumns(a => a.MATERIALNO == input.MATERIALNO.ToUpper())

               .Where(a => a.EID == input.EID)
               .ExecuteCommandAsync();

                    if (count != 1)
                    {
                        throw Oops.Oh("修改失败！");
                    }
                    _SjDb.Ado.CommitTran();
                    return "修改成功！";
                }
                catch (Exception e)
                {
                    _SjDb.Ado.RollbackTran();
                    throw Oops.Oh($"异常：{e.Message}");
                }

        }


        /// <summary>
        /// 增加设备参数记录
        /// </summary>
        /// <returns></returns>
        [HttpPost("/HWYC/AddHWSpecificInformation")]
        public async Task<string> AddHWSpecificInformation(HWSpecificInformation input)
        {
            if (string.IsNullOrEmpty(input.MATERIALNO))
                throw Oops.Oh("料号不能为空");
            if (string.IsNullOrEmpty(input.EQUIPMENTTYPENO))
                throw Oops.Oh("设备类型编号不能为空");
            //判断单据存在
            int cnt = await _SjDb.Queryable<HWSpecificInformation>()
                .Where(a => a.MATERIALNO == input.MATERIALNO)
                .Where(a => a.EQUIPMENTTYPENO == input.EQUIPMENTTYPENO)
                    .Where(a => a.ISDELETE == 0)
                .Select(a => a.EID)
                .CountAsync();

            if (cnt != 0)
                throw Oops.Oh("此设备编号已存在");
            var details = new HWSpecificInformation
            {
                MATERIALNO = input.MATERIALNO.ToUpper(),
                DEVICENUMBER = input.DEVICENUMBER,
                STOVETEMPERATUREMAX = input.STOVETEMPERATUREMAX,
                STOVETEMPERATUREMIN = input.STOVETEMPERATUREMIN,
                BEAKTEMPERATUREMAX = input.BEAKTEMPERATUREMAX,
                BEAKTEMPERATUREMIN = input.BEAKTEMPERATUREMIN,
                GOOSENECKTEMPERATUREMAX = input.GOOSENECKTEMPERATUREMAX,
                GOOSENECKTEMPERATUREMIN = input.GOOSENECKTEMPERATUREMIN,
                EJECTTIMEMAX = input.EJECTTIMEMAX,
                EJECTTIMEMIN = input.EJECTTIMEMIN,
                COOLINGTIMEMAX = input.COOLINGTIMEMAX,
                COOLINGTIMEMIN = input.COOLINGTIMEMIN,
                SPRAYTIMEMAX = input.SPRAYTIMEMAX,
                SPRAYTIMEMIN = input.SPRAYTIMEMIN,
                LOCKMOULDFORCE_MAX = input.LOCKMOULDFORCE_MAX,
                LOCKMOULDFORCEMIN = input.LOCKMOULDFORCEMIN,
                TOTALPRESSUREMAX = input.TOTALPRESSUREMAX,
                TOTALPRESSUREMIN = input.TOTALPRESSUREMIN,
                MRONO = input.MRONO,
                MRONAME = input.MRONAME,
                EQUIPMENTTYPENO = input.EQUIPMENTTYPENO.ToUpper(),
                ITEMNAME = input.ITEMNAME,
                EQUIPMENTSPECS = input.EQUIPMENTSPECS,
                ISDELETE = 0,
                CREATETIME = DateTime.Now,
            };
            #region 插入事务
            try
            {
                //开启事务
                _SjDb.Ado.BeginTran();
                int cnt1 = await _SjDb.Insertable<HWSpecificInformation>(details).ExecuteCommandAsync();

                if (cnt1 == 0)
                {
                    throw Oops.Oh($"新建任务失败！");
                }
                //提交事务
                _SjDb.Ado.CommitTran();
                return "插入成功";
            }
            catch (Exception e)
            {
                //回滚事务
                _SjDb.Ado.RollbackTran();
                throw Oops.Oh($"异常：{e.Message}");
            }
        #endregion
    }
    /// <summary>
    /// 设备参数删除
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [HttpPost("/HWYC/DeleteHWSpecificInformation")]
        public async Task<string> DeleteHWSpecificInformation(HWSpecificInformationInput input)
        {
            //判断单据存在
            int cnt = await _SjDb.Queryable<HWSpecificInformation>()
                .Where(a => a.EID == input.ID)
                .Select(a => a.EID)
                .CountAsync();
            if (cnt == 0)
            {
                throw Oops.Oh("任务不存在，驳回！");
            }
            else
            {
                //提交
                try
                {
                    _SjDb.Ado.BeginTran();
                    int count = await _SjDb.Updateable<HWSpecificInformation>()
                        .SetColumns(a => a.ISDELETE == 1)
                        .Where(a => a.EID == input.ID)
                        .ExecuteCommandAsync();


                    if (count != 1)
                    {
                        throw Oops.Oh("删除失败！");
                    }
                    _SjDb.Ado.CommitTran();
                    return "删除成功！";
                }
                catch (Exception e)
                {
                    _SjDb.Ado.RollbackTran();
                    throw Oops.Oh($"异常：{e.Message}");
                }
            }
        }

        /// <summary>
        /// 设备下拉框列表
        /// </summary>
        /// <returns></returns>
        [HttpPost("/HWYC/GetEquipmentList")]
        public async Task<List<HWEquipmentBasicInformation>> GetPidOrgList()
        {
            return await _SjDb.Queryable<HWEquipmentBasicInformation>()
                        .Where(l => l.LIFECYCLE == 0)

                       .Select(l => new HWEquipmentBasicInformation
                       {
                           DEVICENUMBER = l.DEVICENUMBER,

                       }).ToListAsync();
        }
        #region 设备基础数据导入
        /// <summary>
        /// 设备基础数据导入
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/Imports")]
        public async Task<ActionResult<dynamic>> Imports(IFormFile file)
        {
           

            //判断是否选择文件
            if (file == null || file.Length <= 0)
            {
                return new { success = false, msg = "请选择导入文件！", result = "" };
            }
            //拼接出文件保存到的路径
            var path = Path.Combine(Directory.GetCurrentDirectory(), "File", "Excel");
            //判断文件后缀
            string Express = Path.GetExtension(file.FileName);
            if (Express != ".xlsx" && Express != ".xls")
            {
                throw Oops.Oh("请上传文件扩展名格式为'.xlsx'或'.xls'的文件!");
                //return BadRequest("请上传文件扩展名格式为'.xlsx'的文件!");
            }
            // 文件名md5
            string filename = Path.GetFileNameWithoutExtension(file.FileName) + '-' + Md5FileHash(file.OpenReadStream());
            //文件名+尾缀
            filename += Express;
            //检查是否存在文件夹
            if (false == System.IO.Directory.Exists(path))
            {
                //创建文件夹
                System.IO.Directory.CreateDirectory(path);
            }
            string filePath = path + $@"\{filename}";
            //保存文件
            using (FileStream fs = System.IO.File.Create(filePath))
            {
                file.CopyTo(fs);
                fs.Flush();
            }
            //获取指定地址的文件
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                // 定义一个用于存储excel表数据的集合
                // 以下模拟存储的数据集合对应实体类为T，属性有Name、Age和CreateTime
                List<HWEquipmentBasicInformation> data = new List<HWEquipmentBasicInformation>();
                List<HWEquipmentBasicInformation> updata = new List<HWEquipmentBasicInformation>();
                // 创建工作簿
                IWorkbook workbook = null;
                try
                {
                    // .xlsx格式：XSSFWorkBook
                    if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    // .xls格式：HSSFWorkBook
                    else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0)
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    // 创建一个 FormulaEvaluator 对象
                    var evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();

                    // workbook不为空(文件格式正确)才继续执行
                    if (workbook != null)
                    {
                        // 读取第一个sheet(表)
                        ISheet sheet = workbook.GetSheetAt(0);
                        int rowCount = sheet.LastRowNum;// 获取最后一行的索引值（总行数）
                                                        // 确定表中一定有数据再继续执行
                        if (rowCount > 0)
                        {

                            // 循环填入数据
                            // FirstRowNum 第一个逻辑行
                            for (int a = sheet.FirstRowNum+1; a <= rowCount; a++)
                            {
                                IRow row = sheet.GetRow(a);
                                if (row != null)
                                {
                                            var time = DateTime.Now;
                                            string sbbh = GetCellTextValue(row, 0);
                                            string sblxbh = GetCellTextValue(row, 1);
                                            string byzq = GetCellTextValue(row,2);

                                    //判断单据存在
                                    int count = await _SjDb.Queryable<HWEquipmentBasicInformation>()
                                        .Where(a => a.DEVICENUMBER == sbbh )
                                        .Select(a => a.BID)
                                        .CountAsync();
                                            try
                                            {
                                                _SjDb.Ado.BeginTran();
                                                if (count == 0)//新增数据
                                                {
                                                    HWEquipmentBasicInformation model = new HWEquipmentBasicInformation()
                                                    {
                                                       // BID = Guid.NewGuid().ToString("N"),
                                                        EQUIPMENTTYPENO = sblxbh,
                                                        DEVICENUMBER = sbbh,
                                                        EQUIPMENTSTATE = 0,
                                                        CREATETIME = time,
                                                        LIFECYCLE = 0,
                                                        ARCHIVESTATE=0,
                                                        MAINTENANCEINTERVALS = Convert.ToInt32(GetCellTextValue(row, 2)==""?"0": GetCellTextValue(row, 2)),
                                                      
                                                    };
                                                    data.Add(model);
                                                }
                                                else
                                                {
                                                    //批量修改
                                                    int count1 = await _SjDb.Updateable<HWEquipmentBasicInformation>()
                                                     .SetColumns(a => a.EQUIPMENTTYPENO == sblxbh)
                                                     .SetColumns(a => a.MAINTENANCEINTERVALS == Convert.ToInt32(GetCellTextValue(row, 2) == "" ? "0" : GetCellTextValue(row, 2)))
                                                     .Where(a => a.DEVICENUMBER == sbbh)
                                                     .ExecuteCommandAsync();
                                                }
                                                _SjDb.Ado.CommitTran();
                                            }
                                            catch (Exception e)
                                            {
                                                _SjDb.Ado.RollbackTran();
                                                throw Oops.Oh($"异常：{e.Message}");
                                            }
                                 
                                 
                                }
                            }
                            // 与数据库交互
                            int cnt = await _SjDb.Insertable(data).ExecuteCommandAsync();
                            return "导入成功";
                        }
                        else
                        {
                            return "表中没有数据"; ;
                        }
                    }
                    else
                    {
                        return "导入文件格式有误";
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }

        }
        #endregion

        private string GetCellTextValue(IRow row, int column)
        {
            var cell = row.GetCell(column, MissingCellPolicy.RETURN_BLANK_AS_NULL);

            if (cell != null)
            {
                switch (cell.CellType)
                {
                    case CellType.String:
                        return cell.StringCellValue;

                    case CellType.Numeric:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue.ToString();
                        }
                        else
                        {
                            return cell.NumericCellValue.ToString();
                        }

                    case CellType.Boolean:
                        return cell.BooleanCellValue.ToString();

                    case CellType.Formula:
                        switch (cell.CachedFormulaResultType)
                        {
                            case CellType.String:
                                return cell.StringCellValue;

                            case CellType.Numeric:
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    return cell.DateCellValue.ToString();
                                }
                                else
                                {
                                    return cell.NumericCellValue.ToDouble().ToString("0.00");
                                }

                            case CellType.Boolean:
                                return cell.BooleanCellValue.ToString();

                            default:
                                return string.Empty;
                        }

                    default:
                        return string.Empty;
                }
            }
            return string.Empty;
        }
        /// <summary>
        /// 某文件的md5值
        /// </summary>
        /// <param name="stream">file stream</param>
        /// <returns></returns>
        private string Md5FileHash(Stream stream)
        {
            using var md5 = MD5.Create();
            var data = md5.ComputeHash(stream);
            var sBuilder = new StringBuilder();
            for (var i = 0; i < data.Length; i++)
            {
                sBuilder.Append(data[i].ToString("x2"));
            }
            return sBuilder.ToString();
        }

        #region 设备基础参数数据导入
        /// <summary>
        /// 设备基础参数数据导入
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/ImportPara")]
        public async Task<ActionResult<dynamic>> ImportPara(IFormFile file)
        {


            //判断是否选择文件
            if (file == null || file.Length <= 0)
            {
                return new { success = false, msg = "请选择导入文件！", result = "" };
            }
            //拼接出文件保存到的路径
            var path = Path.Combine(Directory.GetCurrentDirectory(), "File", "Excel");
            //判断文件后缀
            string Express = Path.GetExtension(file.FileName);
            if (Express != ".xlsx" && Express != ".xls")
            {
                throw Oops.Oh("请上传文件扩展名格式为'.xlsx'或'.xls'的文件!");
                //return BadRequest("请上传文件扩展名格式为'.xlsx'的文件!");
            }
            // 文件名md5
            string filename = Path.GetFileNameWithoutExtension(file.FileName) + '-' + Md5FileHash(file.OpenReadStream());
            //文件名+尾缀
            filename += Express;
            //检查是否存在文件夹
            if (false == System.IO.Directory.Exists(path))
            {
                //创建文件夹
                System.IO.Directory.CreateDirectory(path);
            }
            string filePath = path + $@"\{filename}";
            //保存文件
            using (FileStream fs = System.IO.File.Create(filePath))
            {
                file.CopyTo(fs);
                fs.Flush();
            }
            //获取指定地址的文件
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                // 定义一个用于存储excel表数据的集合
                // 以下模拟存储的数据集合对应实体类为T，属性有Name、Age和CreateTime
                List<HWEquipmentBasicInformation> data = new List<HWEquipmentBasicInformation>();
                List<HWEquipmentBasicInformation> updata = new List<HWEquipmentBasicInformation>();
                // 创建工作簿
                IWorkbook workbook = null;
                try
                {
                    // .xlsx格式：XSSFWorkBook
                    if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    // .xls格式：HSSFWorkBook
                    else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0)
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    // 创建一个 FormulaEvaluator 对象
                    var evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();

                    // workbook不为空(文件格式正确)才继续执行
                    if (workbook != null)
                    {
                        // 读取第一个sheet(表)
                        ISheet sheet = workbook.GetSheetAt(0);
                        int rowCount = sheet.LastRowNum;// 获取最后一行的索引值（总行数）
                                                        // 确定表中一定有数据再继续执行
                        if (rowCount > 0)
                        {

                            // 循环填入数据
                            // FirstRowNum 第一个逻辑行
                            for (int a = sheet.FirstRowNum + 1; a <= rowCount; a++)
                            {
                                IRow row = sheet.GetRow(a);
                                if (row != null)
                                {
                                    var time = DateTime.Now;
                                    string sbbh = GetCellTextValue(row, 0);
                                    string sblxbh = GetCellTextValue(row, 1);
                                    string byzq = GetCellTextValue(row, 2);

                                    //判断单据存在
                                    int count = await _SjDb.Queryable<HWEquipmentBasicInformation>()
                                        .Where(a => a.DEVICENUMBER == sbbh)
                                        .Select(a => a.BID)
                                        .CountAsync();
                                    try
                                    {
                                        _SjDb.Ado.BeginTran();
                                        if (count == 0)//新增数据
                                        {
                                            HWEquipmentBasicInformation model = new HWEquipmentBasicInformation()
                                            {
                                                // BID = Guid.NewGuid().ToString("N"),
                                                EQUIPMENTTYPENO = sblxbh,
                                                DEVICENUMBER = sbbh,
                                                EQUIPMENTSTATE = 0,
                                                CREATETIME = time,
                                                LIFECYCLE = 0,
                                                ARCHIVESTATE = 0,
                                                MAINTENANCEINTERVALS = Convert.ToInt32(GetCellTextValue(row, 2) == "" ? "0" : GetCellTextValue(row, 2)),

                                            };
                                            data.Add(model);
                                        }
                                        else
                                        {
                                            //批量修改
                                            int count1 = await _SjDb.Updateable<HWEquipmentBasicInformation>()
                                             .SetColumns(a => a.EQUIPMENTTYPENO == sblxbh)
                                             .SetColumns(a => a.MAINTENANCEINTERVALS == Convert.ToInt32(GetCellTextValue(row, 2) == "" ? "0" : GetCellTextValue(row, 2)))
                                             .Where(a => a.DEVICENUMBER == sbbh)
                                             .ExecuteCommandAsync();
                                        }
                                        _SjDb.Ado.CommitTran();
                                    }
                                    catch (Exception e)
                                    {
                                        _SjDb.Ado.RollbackTran();
                                        throw Oops.Oh($"异常：{e.Message}");
                                    }


                                }
                            }
                            // 与数据库交互
                            int cnt = await _SjDb.Insertable(data).ExecuteCommandAsync();
                            return "导入成功";
                        }
                        else
                        {
                            return "表中没有数据"; ;
                        }
                    }
                    else
                    {
                        return "导入文件格式有误";
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }

        }
        #endregion


        [HttpPost("/HWYC/ExcelInput111")]
        [AllowAnonymous]
        public async void ExcelInput111()
        {
            int ww = 0;
            String ww1 = "";
            String ww2 = "";
            DateTime date = DateTime.Now;

            #region 导入excel
            IWorkbook wk = null;

            string url = App.WebHostEnvironment.WebRootPath + "\\EnvironmentExcel" + "\\2022年生产工单量汇总表(1).xlsx";
            //string url = @"E:\excel\2022年生产工单量汇总表(1).xlsx";
            string extension = Path.GetExtension(url);
            try
            {
                FileStream fs = File.OpenRead(url);
                if (extension.Equals(".xls"))
                {
                    //把xls文件中的数据写入wk中
                    wk = new HSSFWorkbook(fs);
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new XSSFWorkbook(fs);
                }

                fs.Close();
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);

                IRow row = sheet.GetRow(0);  //读取当前行数据

                sheet = wk.GetSheet("Sheet1 (2)");

                List<EquipmentExcel> list = new List<EquipmentExcel>();

                for (int i = 0; i <= sheet.LastRowNum; i++)  //LastRowNum 是当前表的总行数-1（注意）
                {
                    row = sheet.GetRow(i);  //读取当前行数据
                    if (row != null)
                    {
                        EquipmentExcel excel = new EquipmentExcel();
                        //LastCellNum 是当前行的总列数  row.LastCellNum
                        for (int j = 0; j < 22; j++)
                        {

                            if (i > 0)
                            {

                                switch (j)
                                {

                                    case 0:
                                        excel.MATERIALNO = row.GetCell(j).ToString();
                                        break;

                                    case 1:
                                        excel.ITEMNAME = row.GetCell(j).ToString();
                                        break;

                                    case 2:
                                        excel.EQUIPMENTSPECS = row.GetCell(j).ToString();
                                        break;

                                    case 14:
                                        excel.EQUIPMENTTYPENO = row.GetCell(j).ToString();
                                        break;

                                    case 15:
                                        excel.DEVICENUMBER = row.GetCell(j).ToString();
                                        break;

                                    case 16:
                                        excel.CanShu = row.GetCell(j).ToString();
                                        break;

                                    case 17:
                                        excel.Min = row.GetCell(j).ToString();
                                        break;

                                    case 18:
                                        excel.Max = row.GetCell(j).ToString();
                                        break;

                                    case 19:
                                        excel.unit = row.GetCell(j).ToString();
                                        break;

                                    case 20:
                                        excel.MRONO = row.GetCell(j).ToString().Replace("\n",",").TrimEnd(',');
                                        break;

                                    case 21:
                                        excel.MRONAME = "";
                                        break;
                                }




                            }

                        }
                        if (excel != null && !string.IsNullOrEmpty(excel.DEVICENUMBER))
                        {
                            list.Add(excel);
                        }
                    }
                }

             

                //设备详细信息
                List<HWSpecificInformation> hWSpecifics = new List<HWSpecificInformation>();
               
                //a应该小于 list.count
                for (int a = 0; a < list.Count; a++)
                {
                    EquipmentExcel equipment = list[a];

                    List<string> strings = equipment.DEVICENUMBER.Split("\n").ToList();

                    //最大值
                    List<string> maxList = equipment.Max.Split("\n").ToList();
                    //最小值
                    List<string> minList = equipment.Min.Split("\n").ToList();


                 

                    StringBuilder sb = new StringBuilder();

                    if (strings.Count > 1)
                    {

                        for (int i = 0; i < strings.Count; i++)
                        {
                            sb.Append(strings[i]);
                            if (i + 1 < strings.Count)
                            {
                                sb.Append(",");
                            }
                        }

                    }
                    else if (strings.Count == 1)
                    {

                        sb.Append(list[a].DEVICENUMBER);
                    }
                    ww = a;
                    ww1 = equipment.Max;
                    ww2 = equipment.Min;

                    HWSpecificInformation hWSpecificInformation = new HWSpecificInformation();
                    hWSpecificInformation.MATERIALNO = list[a].MATERIALNO;
                    hWSpecificInformation.DEVICENUMBER = sb.ToString();
                    hWSpecificInformation.STOVETEMPERATUREMAX = Convert.ToDouble(maxList[0]);
                    hWSpecificInformation.STOVETEMPERATUREMIN = Convert.ToDouble(minList[0]);
                    hWSpecificInformation.BEAKTEMPERATUREMAX = Convert.ToDouble(maxList[1]);
                    hWSpecificInformation.BEAKTEMPERATUREMIN = Convert.ToDouble(minList[1]);
                    hWSpecificInformation.GOOSENECKTEMPERATUREMAX = Convert.ToDouble(maxList[2]);
                    hWSpecificInformation.GOOSENECKTEMPERATUREMIN = Convert.ToDouble(minList[2]);
                    hWSpecificInformation.EJECTTIMEMAX = Convert.ToDouble(maxList[3]);
                    hWSpecificInformation.EJECTTIMEMIN = Convert.ToDouble(minList[3]);
                    hWSpecificInformation.COOLINGTIMEMAX = Convert.ToDouble(maxList[4]);
                    hWSpecificInformation.COOLINGTIMEMIN = Convert.ToDouble(minList[4]);
                    hWSpecificInformation.SPRAYTIMEMAX = Convert.ToDouble(maxList[5]);
                    hWSpecificInformation.SPRAYTIMEMIN = Convert.ToDouble(minList[5]);
                    hWSpecificInformation.LOCKMOULDFORCE_MAX = Convert.ToDouble(maxList[6]);
                    hWSpecificInformation.LOCKMOULDFORCEMIN = Convert.ToDouble(minList[6]);
                    hWSpecificInformation.TOTALPRESSUREMAX = Convert.ToDouble(maxList[7]);
                    hWSpecificInformation.TOTALPRESSUREMIN = Convert.ToDouble(minList[7]);
                    hWSpecificInformation.MRONO = list[a].MRONO;
                    hWSpecificInformation.MRONAME = list[a].MRONAME;
                    hWSpecificInformation.EQUIPMENTTYPENO = list[a].EQUIPMENTTYPENO;
                    hWSpecificInformation.ITEMNAME = list[a].ITEMNAME;
                    hWSpecificInformation.EQUIPMENTSPECS = list[a].EQUIPMENTSPECS;
                    hWSpecificInformation.CREATETIME = date;

                    hWSpecifics.Add(hWSpecificInformation);

                }

              
                #region 插入事务
                try
                {
                    //开启事务
                    _SjDb.Ado.BeginTran();
                   // int count = await _SjDb.Insertable<HWEquipmentBasicInformation>(hWEquipment[0]).ExecuteCommandAsync();

                    int count2 = await _SjDb.Insertable<HWSpecificInformation>(hWSpecifics).ExecuteCommandAsync();

                    if (count2 == 0)
                    {
                        throw Oops.Oh($"新建任务失败！");
                    }
                    //提交事务
                    _SjDb.Ado.CommitTran();
                    // return "插入成功";
                }
                catch (Exception e)
                {
                    //回滚事务
                    _SjDb.Ado.RollbackTran();
                    throw Oops.Oh($"异常：{e.Message}");
                }
                #endregion



            }

            catch (Exception e)
            {
                //只在Debug模式下才输出
                Console.WriteLine(e.Message);
                Console.WriteLine(ww);
                Console.WriteLine(ww1);
                Console.WriteLine(ww2);
            }



            #endregion

           

          
        }
    }
}